﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;
using BorderStyle = NPOI.SS.UserModel.BorderStyle;

namespace HosBoneExcell
{
    class NPOI_Helper
    {
        /// <summary>
        /// 模板文件默认路径
        /// </summary>
        public static String modelExlPath = @"template.xls";
        
        /// <summary>
        /// 读取表格模板信息
        /// </summary>
        /// <param name="_modelExlPath"></param>
        /// <returns></returns>
        public static HssModelBean ReadModel(string _ExlPath, int _sheetindex)
        {
            String templatePath;
#if (!DEBUG)
            templatePath = modelExlPath; ///< Application.StartupPath + @"\plugsins";
#else
            templatePath = modelExlPath;
#endif
            ///< 状态返回+包含表格操作对象
            HssModelBean hssModelBean = new HssModelBean();

            ///< 没有就拷贝一个模板来用
            if (!File.Exists(_ExlPath))
            {
                File.Copy(templatePath, _ExlPath);
            }

            ///< 创建一个excel对象
            HSSFWorkbook hssfworkbookDown = null;
            ///< 读入刚复制的要导出的excel文件
            try
            {
                using (FileStream file = new FileStream(_ExlPath, FileMode.Open, FileAccess.Read))  ///< 路径，打开权限，读取权限
                {
                    hssfworkbookDown = new HSSFWorkbook(file);
                    file.Close();
                }
            }
            catch(IOException e)
            {
                hssModelBean.state = false;
                hssModelBean.message = e.Message;
                return hssModelBean;
            }

            ///< 模版的一个页面在GetSheetAt方法中，这里取第一个页面是0
            if (_sheetindex > (hssfworkbookDown.Count - 1))
            {
                hssModelBean.state = false;
                hssModelBean.message = "没有那么多表格页!";
                return hssModelBean;
            }
            hssModelBean.state = true;
            hssModelBean.hSSFSheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(_sheetindex);
            hssModelBean.hssfworkbookDown = hssfworkbookDown;

            return hssModelBean;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="_hssModelBean"></param>
        public static bool updateData(HssModelBean _hssModelBean, String _outExcellFile, List<String> dataList)
        {
            ///< 模版的一个页面在GetSheetAt方法中，这里取第一个页面是0
            HSSFSheet sheet1 = _hssModelBean.hSSFSheet;
            ///< 行数(下一行就是从这里开始写入，注意是从0开始的)
            int rowNums = sheet1.PhysicalNumberOfRows;
            ///< 获取模板行(目前是第1行的个数，作为插入数据的列数)
            int modelCellCount = sheet1.GetRow(rowNums - 1).Cells.Count;

            ///< 创建
            IRow row = sheet1.CreateRow(rowNums);
            ///< 创建单元格样式
            ICellStyle iCellStyle = getICellStyle(_hssModelBean.hssfworkbookDown);

            for (int i = 0; i < modelCellCount && i < dataList.Count; ++i)
            {
                ICell celld = row.CreateCell(i);    ///< 在第1行中创建单元格第i列
                celld.SetCellValue(dataList[i]);  ///< 设置内容
                setICellStyle(celld, iCellStyle);   ///< 设置边框样式
            }

            ///< 创建文件
            using (FileStream files = new FileStream(_outExcellFile, FileMode.OpenOrCreate))
            {
                try
                {
                    _hssModelBean.hssfworkbookDown.Write(files);
                    files.Close();

                }
                catch (IOException)
                {
                    return false;
                }
                finally
                {
                    _hssModelBean.hssfworkbookDown.Clear();
                    _hssModelBean.hssfworkbookDown.Close();
                }
            }

            return true;
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="_cell"></param>
        /// <param name="_hSSFWorkbook"></param>
        public static void setICellStyle(ICell _cell, HSSFWorkbook _hSSFWorkbook)
        {
            setICellStyle(_cell, getICellStyle(_hSSFWorkbook));
        }

        /// <summary>
        /// 设置单元格样式
        /// </summary>
        /// <param name="_cell"></param>
        /// <param name="_cellStyle"></param>
        public static void setICellStyle(ICell _cell, ICellStyle _cellStyle)
        {
            _cell.CellStyle = _cellStyle;
        }

        /// <summary>
        /// 创建单元格样式
        /// </summary>
        /// <param name="_hSSFWorkbook"></param>
        /// <returns></returns>
        public static ICellStyle getICellStyle(HSSFWorkbook _hSSFWorkbook)
        {
            ICellStyle style = _hSSFWorkbook.CreateCellStyle();
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            return style;
        }
    }
}
